package com.eascs.admin.o2o.loanAccount.dao;

import java.util.GregorianCalendar;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.loanAccount.vo.O2oCollectFeesVo;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.sso.UserHolder;

@Repository
public class O2oCollectFeesDao extends BaseO2oDao {

	public PageResult<Object[]> queryByPageCollectFee(O2oCollectFeesVo vo) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT collect.id,collect.bill_code,collect.create_name,collect.ltd_name,collect.loan_name,");
		querySql.append(" collect.document_no,collect.collect_money,collect.create_date,collect.status");
		querySql.append(" from o2o_collect_fees collect where  ");
		if (UserHolder.getUser().isSuperAdministrator() || vo.isQueryAll()) {
			querySql.append(" collect.is_del='N' ");
		} else {
//			querySql.append(" /**DATA_PERMISSION(collect.ltd)**/ collect.is_del='N' ");
			querySql.append(" collect.create_id='"+UserHolder.getUser().getNo()+"' ");
		}
		if (StringUtil.isNotBlank(vo.getLoanName())) {
			querySql.append(" and collect.loan_name like ? ");
			pageQuery.addLikeParam(vo.getLoanName());
		}
		if (StringUtil.isNotBlank(vo.getDocumentNo())) {
			querySql.append(" and collect.document_no like ? ");
			pageQuery.addLikeParam(vo.getDocumentNo());
		}
		if (StringUtil.isNotBlank(vo.getStatus())) {
			querySql.append(" and collect.status =? ");
			pageQuery.addParam(vo.getStatus());
		}

		if (StringUtil.hasText(vo.getBeginCreateDate())) {
			querySql.append(" and collect.create_date >= ? ");
			pageQuery.addParam(vo.getBeginCreateDate());
		}
		if (StringUtil.hasText(vo.getEndCreateDate())) {
			querySql.append(" and collect.create_date <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(vo.getEndCreateDate()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}

		if (StringUtil.isNotBlank(vo.getCreateName())) {
			querySql.append(" and collect.create_name like ? ");
			pageQuery.addLikeParam(vo.getCreateName());
		}

		if (StringUtil.isNotBlank(vo.getLtdName())) {
			querySql.append(" and collect.ltd_name like ? ");
			pageQuery.addLikeParam(vo.getLtdName());
		}

		querySql.append(" order by collect.create_date desc ");
		pageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	public PageResult<Object[]> queryByPageCollectFeeExport(O2oCollectFeesVo vo) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT collect.bill_code,collect.create_name,collect.ltd_name,collect.loan_name,");
		querySql.append(" collect.document_no,collect.collect_money,DATE_FORMAT(collect.create_date,'%Y-%m-%d'),collect.collect_no ");
		querySql.append(" ,(case when collect.status='NEW' then '新建' when collect.status='RUN' then '审核中' when collect.status='CLO' then '流程结束' end) ");
		querySql.append(" from o2o_collect_fees collect where ");
		if (vo.isQueryAll()) {
			querySql.append(" collect.is_del='N' ");
		} else {
			querySql.append(" /**DATA_PERMISSION(collect.ltd)**/ collect.is_del='N' ");
		}
		if (StringUtil.isNotBlank(vo.getLoanName())) {
			querySql.append(" and collect.loan_name like ? ");
			pageQuery.addLikeParam(vo.getLoanName());
		}
		if (StringUtil.isNotBlank(vo.getDocumentNo())) {
			querySql.append(" and collect.document_no like ? ");
			pageQuery.addLikeParam(vo.getDocumentNo());
		}
		if (StringUtil.isNotBlank(vo.getStatus())) {
			querySql.append(" and collect.status =? ");
			pageQuery.addParam(vo.getStatus());
		}
		
		if (StringUtil.hasText(vo.getBeginCreateDate())) {
			querySql.append(" and collect.create_date >= ? ");
			pageQuery.addParam(vo.getBeginCreateDate());
		}
		if (StringUtil.hasText(vo.getEndCreateDate())) {
			querySql.append(" and collect.create_date <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(vo.getEndCreateDate()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (StringUtil.isNotBlank(vo.getCreateName())) {
			querySql.append(" and collect.create_name like ? ");
			pageQuery.addLikeParam(vo.getCreateName());
		}
		
		if (StringUtil.isNotBlank(vo.getLtdName())) {
			querySql.append(" and collect.ltd_name like ? ");
			pageQuery.addLikeParam(vo.getLtdName());
		}
		
		querySql.append(" order by collect.create_date desc ");
		pageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(pageQuery);
	}

}
